﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.IO;
using System.Diagnostics;
using OfficeOpenXmlCrypto;
using System.Globalization;

namespace MoneyWentWhere.DataModel.Storage
{
    public class Excel2007Format : DataFormatX, IHasPassword
    {
        static readonly String TabPrefix = Char.ConvertFromUtf32(0x25AA);
        const String DateFormat = "yyyy-MM-ddThh:mm:ss";

        const String ColId = "ID#";

        const String TabMww = "MWW";
        const String TabAccounts = "Accounts";
        const String TabTransactions = "Transactions";

        public String Password { get; set; } 

        public override void Save(MoneyDataSet ds, string filename)
        {
            ds.Filename = filename;
            ds.DataFormat = this;

            OfficeCryptoStream stream = ds.ExtendedFileData as OfficeCryptoStream;
            if (stream == null)
            {
                stream = OfficeCryptoStream.Create(filename);
            }
            stream.Password = Password;
            stream.Seek(0, SeekOrigin.Begin);

            using (ExcelPackage package = new ExcelPackage(stream))
            {
                ExcelWorkbook wb = package.Workbook;

                WriteMWW(wb, ds);
                WriteAccounts(wb, ds);
                WriteTransactions(wb, ds);

                package.Save();
            }
            stream.SaveAs(filename);

            ds.ExtendedFileData = stream;
            // NOT closing the stream, that should 
            // be done when MoneyDataSet is disposed
        }

        public override MoneyDataSet Load(string filename)
        {
            MoneyDataSet ds = new MoneyDataSet();

            OfficeCryptoStream stream;
            try
            {
                stream = OfficeCryptoStream.Open(filename, Password);
            }
            catch (OfficeOpenXmlCrypto.InvalidPasswordException e)
            {
                // Re-throw inside our own password exception
                throw new InvalidPasswordException("Invalid password.", e);
            }

            using (ExcelPackage package = new ExcelPackage(stream))
            {
                ExcelWorkbook wb = package.Workbook;
                ReadMWW(wb, ds);
                ReadAccounts(wb, ds);
                ReadTransactions(wb, ds);
            }

            ds.ExtendedFileData = stream;
            ds.Filename = filename;
            ds.DataFormat = this;
            ds.Password = Password;
            return ds;
        }

        void WriteMWW(ExcelWorkbook wb, MoneyDataSet ds)
        {            
            ExcelWorksheet ws = wb.Worksheets.GetOrCreateWorksheet(TabMww);
            //ws.Hidden = true;

            int row = 1;
            ws.WriteRow("WsTitle", row++, "MWW General Info");
            ws.WriteRow(row++, "Application", "MoneyWentWhere");
            ws.WriteRow(row++, "Version", "1.2");
            ws.WriteRow(row++, "DefaultCurrency", ds.DefaultCurrency);
        }
        
        void ReadMWW(ExcelWorkbook wb, MoneyDataSet ds)
        {
            ExcelWorksheet ws = wb.Worksheets[TabMww];
            if (ws == null) { return; }

            // Build a dict of values
            Dictionary<String, String> dict = new Dictionary<string, string>();
            int row = 1;
            while (true)
            {
                if (ws.RowIsEmpty(row, 1)) { break; }
                dict.AddOrReplace(
                    ws.Cell(row, 1).Value,
                    ws.Cell(row, 2).Value);
                ++row;
            }

            // Assign values
            String curName = dict.GetOrDefault("DefaultCurrency", "USD");
            ds.DefaultCurrency = new Currency(curName);
            ds.Currencies.Add(ds.DefaultCurrency);
        }

        String[] ColAccounts = new String[] {
                    ColId, 
                    "Name",
                    "Currency", 
                    "Comment"};

        void WriteAccounts(ExcelWorkbook wb, MoneyDataSet ds)
        {
            // TODO: instead of deleting, extend/trim
            wb.Worksheets.Delete(TabAccounts);

            // Ac info
            ExcelWorksheet ws = wb.Worksheets.Add(TabAccounts, 2 + ds.Accounts.Count, ColAccounts.Length);

            int row = 1;
            ws.WriteRow("WsTitle", row++, TabAccounts);
            ws.WriteRow(row++, ColAccounts);

            foreach (Account ac in ds.Accounts)
            {
                ws.WriteRow(row++,
                    ac.Guid,
                    ac.Name,
                    ac.Currency,
                    ac.Comment);
            }

            wb.Worksheets.DefinedNames["RangeAccounts"] =
                ExcelDefinedNames.GetRangeRef(TabAccounts,
                    2, ds.Accounts.Count + 1, 1, ColAccounts.Length);

        }

        void ReadAccounts(ExcelWorkbook wb, MoneyDataSet ds)
        {
            ExcelWorksheet ws = wb.Worksheets[TabAccounts];
            if (ws == null) { return; }

            int row = 3; // Skip title, headers
            while (true)
            {
                if (ws.RowIsEmpty(row, ColAccounts.Length)) { break; }

                int col = 1;

                Guid guid = Guid.Empty;                
                Guid_TryParse(ws.Cell(row, col++).Value, out guid);

                String name = ws.Cell(row, col++).Value;
                Currency currency = new Currency(ws.Cell(row, col++).Value);
                String comment = ws.Cell(row, col++).Value;

                Account ac = new Account(ds, guid, name);
                ac.Currency = currency;
                ds.Accounts.Add(ac);

                ++row;
            }
        }

        String[] ColTransactions = new String[] {
                    ColId, 
                    "Account",
                    "Date", 
                    "Amount", 
                    "Comment", 
                    "Tags", 
                    "Kind",
                    "TransferToTx",

                    // Special field
                    "TxSettings",
        };

        void WriteTransactions(ExcelWorkbook wb, MoneyDataSet ds)
        {
            // Put default currency into headers
            for (int i=0; i<ColTransactions.Length; i++)
            {
                ColTransactions[i] = ColTransactions[i].Replace("XXX", ds.DefaultCurrency.Id);
            }

            // TODO: instead of deleting the tab, extend or trim
            wb.Worksheets.Delete(TabTransactions);

            int totalTxCount = 0;
            foreach (Account ac in ds.Accounts) { totalTxCount += ac.Transactions.Count; }
            ExcelWorksheet ws = wb.Worksheets.Add(TabTransactions, 2 + totalTxCount, ColTransactions.Length);

            int row = 1;
            ws.WriteRow("WsTitle", row++, TabTransactions);
            ws.WriteRow(row++, ColTransactions);

            ExchangeRatesFromCurrency rates = new ExchangeRatesFromCurrency(ds.DefaultCurrency);
            rates.FillFromTransfers(ds);

            foreach (Account ac in ds.Accounts) 
            {
                foreach (Transaction tx in ac.Transactions)
                {
                    String ttTxGuid = tx.TransferToTx == null ?
                        "" : tx.TransferToTx.Guid.ToString();

                    ws.WriteRow(row,
                        tx.Guid, 
                        tx.Account.Guid,
                        tx.Date.ToOADate(),
                        tx.Amount, 
                        tx.Comment,
                        tx.Tags.ToString(),
                        tx.Kind,
                        ttTxGuid,
                        // Extra derived columns (not used for import)
                        "" // Special *import-only* column
                        );

                    // Exchange rate, amount, balance in default currency
                    decimal xr = 1 / rates.GetRate(tx.Account.Currency, tx.Date).Rate;
                    int xrCol = ColTransactions.Length - 2;
                    ws.Cell(row, xrCol).Value = xr.ToString();
                    String xrAddr = ExcelCell.GetCellAddress(row, xrCol);

                    ws.Cell(row, xrCol + 1).Formula = ExcelCell.GetCellAddress(row, 4) + " * " + xrAddr;
                    ws.Cell(row, xrCol + 2).Formula = ExcelCell.GetCellAddress(row, 12) + " * " + xrAddr;

                    ++row;
                }
            }

            wb.Worksheets.DefinedNames["RangeTransactions"] = 
                ExcelDefinedNames.GetRangeRef(TabTransactions, 
                    2, totalTxCount + 1, 1, ColTransactions.Length);
        }
       
        void ReadTransactions(ExcelWorkbook wb, MoneyDataSet ds)
        {
            ExcelWorksheet ws = wb.Worksheets[TabTransactions];
            if (ws == null) { return; }

            Dictionary<Transaction, TxSettings> importTxSettings
                = new Dictionary<Transaction, TxSettings>();

            int row = 3; // Skip title, headers
            while (true)
            {
                if (ws.RowIsEmpty(row, ColTransactions.Length)) { break; }

                CreateTransaction(row, ws, ds, importTxSettings);
                ++row;
            }

            // Apply import settings
            foreach (Transaction tx in importTxSettings.Keys)
            {
                tx.Settings = importTxSettings[tx];
            }

        }

        void CreateTransaction(int row, ExcelWorksheet ws, MoneyDataSet ds,
            Dictionary<Transaction, TxSettings> importSettings)
        {
            int col = 1;

            // Id must be the first column.
            Guid guid = Guid.Empty;
            Guid_TryParse(ws.Cell(row, col++).Value, out guid);

            // Account
            String acIdOrName = ws.Cell(row, col++).Value;
            Account ac = FindOrCreateAccount(ds, acIdOrName);

            DateTime date;
            if (!Date_TryParse(ws.Cell(row, col++).Value, out date))
            {
                date = DateTime.Now;
            }

            decimal amount = 0;
            decimal.TryParse(ws.Cell(row, col++).Value, out amount);

            String comment = ws.Cell(row, col++).Value;

            TagSet tags = TagSet.Parse(ws.Cell(row, col++).Value);

            TransactionKind kind;
            String kindStr = ws.Cell(row, col++).Value;
            if (kindStr == "Transfer") { kind = TransactionKind.Transfer; }
            else if (kindStr == "Reconcile") { kind = TransactionKind.Reconcile; }
            else { kind = TransactionKind.Normal; }

            //EnumX.TryParse<TransactionKind>(ws.Cell(row, col++).Value, out kind);

            Guid transferToTxGuid = Guid.Empty;
            Guid_TryParse(ws.Cell(row, col++).Value, out transferToTxGuid);

            Transaction tx = new Transaction(guid, ac, kind, date,
                transferToTxGuid, amount, comment, tags);
            ac.Transactions.Add(tx);

            // Import settings
            String settingsRep = ws.Cell(row, col++).Value.Trim();
            if (settingsRep != "") 
            { 
                importSettings.Add(tx, TxSettings.Parse(settingsRep)); 
            }
        }

        Account FindOrCreateAccount(MoneyDataSet ds, String acIdOrName)
        {
            Account ac = null;

            Guid guid = Guid.Empty;
            Guid_TryParse(acIdOrName, out guid);

            // Find account by guid
            if (guid != Guid.Empty)
            {
                ac = ds.GetObject<Account>(guid);
            }

            if (ac == null) 
            {
                // Find account by name
                foreach (Account acX in ds.Accounts)
                {
                    if (acX.Name.Equals(acIdOrName, StringComparison.InvariantCultureIgnoreCase))
                    {
                        ac = acX;
                        break;
                    }
                }
            }

            if (ac == null)
            {
                // Create provisional account
                ac = new Account(ds, Guid.NewGuid(), "[??] " + acIdOrName);
            }

            return ac;
        }

        bool Date_TryParse(String rep, out DateTime date)
        {
            date = DateTime.MinValue;

            // OA date is default
            double oaDate;
            if (double.TryParse(rep, out oaDate))
            {
                date = DateTime.FromOADate(oaDate);
                return true;
            }

            // ISO date is desired
            if (DateTime.TryParseExact(rep, DateFormat,
                    CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
            {
                return true;
            }

            // Try to extract any date
            if (DateTime.TryParse(rep, out date))
            {
                return true;
            }

            return false;
        }

        bool Guid_TryParse(String rep, out Guid guid)
        {
            guid = Guid.Empty;
            
            rep = rep.Trim();
            if (String.IsNullOrEmpty(rep)) { return true; }

            try
            {
                guid = new Guid(rep); 
            }
            catch (FormatException)
            {
                // Cannot parse Guid
                return false;
            }
            return true;
        }

        public override void Save(MoneyDataSet ds, Stream outStream)
        {
            throw new InvalidOperationException("Not supported in Excel format.");
        }

        public override MoneyDataSet Load(Stream inStream)
        {
            throw new InvalidOperationException("Not supported in Excel format.");
        }

        public override bool AcceptsFile(string filename)
        {
            return Path.GetExtension(filename).Equals(".xlsx", 
                StringComparison.InvariantCultureIgnoreCase);
        }

        #region Report Serialization
        /*
        public static void SaveReport(TableReport report, String filename, String password)
        {
            OfficeCryptoStream stream = OfficeCryptoStream.Create(filename);
            stream.Password = password;
            stream.Seek(0, SeekOrigin.Begin);

            using (ExcelPackage package = new ExcelPackage(stream))
            {
                ExcelWorksheets doc = package.Workbook.Worksheets;
                WriteReport(report, doc);

                package.Save();
            }
            stream.SaveAs(filename);

            stream.Close();
        }

        private static void WriteReport(TableReport report, ExcelWorksheets doc)
        {
            if (report.Rows == null) { report.CreateReport(); }

            String name = "Report";
            ExcelWorksheet ws = doc.Add(name, report.Rows.Count + 4, report.Columns.Count + 1);
            int rowNum = 1;
            ws.WriteRow(rowNum++, name);
            ws.WriteRow(rowNum++, DateTime.Now.ToOADate(), "<< Date");

            // Output columns
            int colNum = 2;
            foreach (TableReportColumn col in report.Columns)
            {
                ws.Cell(rowNum, colNum).Value = col.Key.Currency.Id;
                ws.Cell(rowNum+1, colNum).Value = col.Key.Account.Name;
                if (col.Key.Kind == TransactionKind.Normal)
                {
                    ws.Cell(rowNum+2, colNum).Value = 
                        (col.Key.IsDeposit ? "+" : "-") + " " + col.Key.Category;
                }
                else 
                {
                    ws.Cell(rowNum+2, colNum).Value = col.Key.Kind.ToString();
                }
                colNum++;
            }
            rowNum += 3;

            // Output rows
            foreach (TableReportRow row in report.Rows)
            {
                DateTime endDate = row.StartDate.Add(row.StartDate.PeriodDuration(row.PeriodUnit)).AddMinutes(-1);
                ws.Cell(rowNum, 1).Value = endDate.ToOADate().ToString();

                for (int i = 0; i < row.Cells.Length; i++)
                {
                    ws.Cell(rowNum, i+2).Value = row.CellAmount(i).ToString();
                }
                rowNum++;
            }

            ws.WriteRow(rowNum++, "End Report");
        }
         */
        #endregion
    }
}
